package com.version2_0.dao.implement;

import com.version2_0.dao.PageDao2_0;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by reeveslee on 16/6/7.
 */
@Repository
public class PageDaoImpl2_0 implements PageDao2_0 {
    private static final Logger LOGGER = Logger.getLogger(PageDaoImpl2_0.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取数据库某一特定信息数据
     *
     * @param type
     * @param subId
     * @return
     */
    @Override
    public Map<String, Object> getSectionInfo(int type, int subId) {
        String sql = "";
        if (type == 1) {
            // 小图格式信息
            sql = "SELECT id AS pageId,titleImage,title,source,readNumber FROM v2_pages WHERE id=?";
        }

        if (type == 2) {
            // 大图格式信息
            sql = "SELECT id AS pageId,titleImage,title,secondary,source,readNumber,tag FROM v2_pages WHERE id=?";
        }

        if (type == 3) {
            // 投票格式信息
            sql = "SELECT id,title,one,second,oneSupport,secondSupport FROM v2_votes WHERE id=?";
        }

        if (type == 4) {
            // 视频样式信息
            sql = "SELECT link,title,source,readNumber FROM v2_vedios WHERE id=?";
        }

        if (type == 5) {
            // 话题样式信息
            sql = "SELECT image,topicId,title FROM v2_topic WHERE id=?";
        }

        if (type == 6) {
            // 三个图样式信息
            sql = "SELECT id AS pageId,titleImage,title,source,readNumber FROM v2_pages WHERE id=?";
        }

        if (type == 7) {
            // 纯文字样式信息
            sql = "SELECT id AS pageId,text,source,readNumber FROM v2_pages WHERE id=?";
        }

        if (type == 8) {
            // 历史上今天样式信息
            sql = "SELECT id AS pageId,titleImage,title,secondary FROM v2_pages WHERE id=?";
        }

        return jdbcTemplate.queryForMap(sql, new Object[]{subId});
    }

    /**
     * 获取某一版块的信息数据
     *
     * @param sectionId
     * @param timestamp
     * @param lastId
     * @return
     */
    @Override
    public List<Map<String, Object>> pages(int sectionId, long timestamp, int lastId) {
        String sql = "";
        if (timestamp == 0) {
            // 第一次请求数据
            sql = "SELECT id,type,subId FROM v2_infos WHERE sectionId=? ORDER BY id DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{sectionId, 30});
        } else if (lastId == 0) {
            // 获取增量第一页数据
            sql = "SELECT id,type,subId FROM v2_infos WHERE sectionId=? AND time>? ORDER BY id DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{sectionId, timestamp, 30});
        } else {
            // 获取增量非第一页
            sql = "SELECT id,type,subId FROM v2_infos WHERE sectionId=? AND id<? ORDER BY id DESC LIMIT ?";
            return jdbcTemplate.queryForList(sql, new Object[]{sectionId, lastId, 30});
        }
    }

    @Override
    public Map<String, Object> page(int pageId) {
        String sql = "SELECT v2_pages.titleImage,title,pageContent,source,v2_pages.readNumber,time FROM v2_pages,page_info WHERE page_info.pageId=v2_pages.pageId AND id=?";
        try {
            return jdbcTemplate.queryForMap(sql, new Object[]{pageId});
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> recoms(int pageId) {
        String sql = "SELECT id AS pageId,titleImage,title,source,readNumber FROM v2_pages WHERE sectionId=(select sectionId FROM v2_pages WHERE id=?) ORDER BY readNumber DESC LIMIT 2";
        return jdbcTemplate.queryForList(sql, new Object[]{pageId});
    }

    @Override
    public int pageComNumber(int pageId) {
        String sql = "SELECT COUNT(*) FROM v2_pagecomment WHERE pageId=?";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{pageId}, java.lang.Integer.class);
        } catch (Exception e) {
            return 0;
        }
    }

    @Override
    public boolean hasCollect(int pageId, int userId) {
        String sql = "SELECT id FROM v2_pagecollect WHERE userId=? AND pageId=?";
        try {
            int row = jdbcTemplate.queryForObject(sql, new Object[]{userId, pageId}, java.lang.Integer.class);
            return (row == 1) ? true : false;
        } catch (EmptyResultDataAccessException e) {
            return false;
        }
    }

    @Override
    public List<Map<String, Object>> comment(int pageId, int lastId) {
        if (lastId == 0) {
            String sql = "SELECT v2_pagecomment.id,v2_pagecomment.userId,userName,userPhoto,text,images,time,praise FROM v2_pagecomment,user_info WHERE v2_pagecomment.userId=user_info.userId AND v2_pagecomment.pageId=? ORDER BY v2_pagecomment.id DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql, new Object[]{pageId});
        } else {
            String sql = "SELECT id,v2_pagecomment.userId,userName,userPhoto,text,images,time,praise FROM v2_pagecomment,user_info WHERE v2_pagecomment.userId=user_info.userId AND v2_pagecomment.pageId=? AND v2_pagecomment.id<? ORDER BY v2_pagecomment.id DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql, new Object[]{pageId, lastId});
        }

    }

    @Override
    public boolean hasPraised(int commentId, int userId) {
        String sql = "SELECT id FROM v2_commentpraise WHERE userId=? AND commentId=?";
        try {
            jdbcTemplate.queryForObject(sql, new Object[]{userId, commentId}, java.lang.Integer.class);
            return true;
        } catch (EmptyResultDataAccessException e) {
            return false;
        }
    }

    @Override
    public List<Map<String, Object>> reply(int commentId) {
        String sql = "SELECT id,v2_pagecommentreply.userId,userName,userPhoto,text,time FROM v2_pagecommentreply,user_info WHERE v2_pagecommentreply.userId=user_info.userId AND commentId=? ORDER BY time DESC";
        return jdbcTemplate.queryForList(sql, new Object[]{commentId});
    }

    @Override
    public boolean addComment(int userId, int pageId, String text, String images) {
        String sql = "INSERT INTO v2_pagecomment(userId,pageId,text,images,time) VALUES (?,?,?,?,?)";
        int row = jdbcTemplate.update(sql, new Object[]{userId, pageId, text, images, new Date()});
        return (row == 1) ? true : false;
    }

    @Override
    public boolean delComment(int userId, int commentId) {
        String sql = "DELETE FROM v2_pagecomment WHERE userId=? AND id=?";
        int row = jdbcTemplate.update(sql, new Object[]{userId, commentId});
        return (row == 1) ? true : false;
    }

    @Override
    public Map<String, Object> voteChose(int userId, int voteInfoId, int chose) {

        // 使用存储过程,在插入用户投票新纪录的时候,改变投票中两个选项的投票数
        String sql = "INSERT INTO v2_uservote(infoId,userId,chose,time) VALUES (?,?,?,?)";
        int row = jdbcTemplate.update(sql, new Object[]{voteInfoId, userId, chose, new Date()});

        sql = "SELECT oneSupport,secondSupport FROM v2_votes,v2_infos WHERE v2_infos.subId=v2_votes.id AND v2_infos.id=?";
        return jdbcTemplate.queryForMap(sql, new Object[]{voteInfoId});
    }

    @Override
    public List<Map<String, Object>> history(int lastId) {

        if (lastId == 0) {
            String sql = "SELECT id,titleImage,title,secondary,time FROM v2_pages WHERE type=8 ORDER BY time DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql);
        } else {
            String sql = "SELECT id,titleImage,title,secondary,time FROM v2_pages WHERE type=8 AND id<? ORDER BY time DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql, new Object[]{lastId});
        }
    }

    @Override
    public List<Map<String, Object>> video(int lastId) {

        if (lastId == 0) {
            String sql = "SELECT id,title,link,source,readNumber,time FROM v2_vedios ORDER BY time DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql);
        } else {
            String sql = "SELECT id,title,link,source,readNumber,time FROM v2_vedios WHERE id<? ORDER BY time DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql, new Object[]{lastId});
        }
    }

    @Override
    public boolean checkVote(int userId, int infoId) {
        String sql = "SELECT id FROM v2_uservote WHERE userId=? AND infoId=? LIMIT 1";

        try {
            int id = jdbcTemplate.queryForObject(sql, new Object[]{userId, infoId}, java.lang.Integer.class);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public List<Map<String, Object>> vote(int lastId) {

        if (lastId == 0) {
            String sql = "SELECT id,title,one,second,img,oneSupport,secondSupport FROM v2_votes ORDER BY id DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql);
        } else {
            String sql = "SELECT id,title,one,second,img,oneSupport,secondSupport FROM v2_votes WHERE id<? ORDER BY id DESC LIMIT 20";
            return jdbcTemplate.queryForList(sql, new Object[]{lastId});
        }


    }
}
